package db.mysql;

import java.sql.*;

public class MySqlConnectUtil {

    /**
     * 批量预处理修改/删除
     * @throws SQLException
     */
    public static void preBatchUpdateDelete() throws SQLException {
        Connection connection = getConnection("ip地址", 3306, "username", "password", "database");
        connection.setAutoCommit(false);
        PreparedStatement statement = connection.prepareStatement("sql");
        for (int i = 0; i < 10; i++) {
            statement.setString(0, "值");
            statement.addBatch();
        }
        int i = statement.executeUpdate();
        connection.commit();
    }

    /**
     * 预处理修改/删除
     * @throws SQLException
     */
    public static void preUpdateDelete() throws SQLException {
        Connection connection = getConnection("ip地址", 3306, "username", "password", "database");
        PreparedStatement statement = connection.prepareStatement("sql");
        statement.setString(0, "值");
        int i = statement.executeUpdate();
    }

    /**
     * 修改/删除
     * @throws SQLException
     */
    public static void updateDelete() throws SQLException {
        Connection connection = getConnection("ip地址", 3306, "username", "password", "database");
        Statement statement = connection.createStatement();
        int i =  statement.executeUpdate("sql");
    }

    /**
     * 预处理查询
     * @throws SQLException
     */
    public static void preSelect() throws SQLException {
        Connection connection = getConnection("ip地址", 3306, "username", "password", "database");
        PreparedStatement statement = connection.prepareStatement("sql");
        statement.setString(0, "值");
        ResultSet resultSet =  statement.executeQuery();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); //列的元数据信息 数量、类型、属性。
        System.out.println(resultSetMetaData);
        while (resultSet.next()) {
            System.out.println(resultSet.getString("columnName")); //根据列的字段返回数据
            System.out.println(resultSet.getString(0)); //根据索引返回数据
        }
    }

    /**
     * 简单查询
     * @throws SQLException
     */
    public static void select() throws SQLException {
        Connection connection = getConnection("ip地址", 3306, "username", "password", "database");
        Statement statement = connection.createStatement();
        ResultSet resultSet =  statement.executeQuery("sql");
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); //列的元数据信息 数量、类型、属性。
        System.out.println(resultSetMetaData);
        while (resultSet.next()) {
            System.out.println(resultSet.getString("columnName")); //根据列的字段返回数据
            System.out.println(resultSet.getString(0)); //根据索引返回数据
        }
    }

    public static Connection getConnection(String ip, int port, String username, String password, String database) {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = new StringBuffer().append("jdbc:mysql://")
                    .append(ip).append(":").append(port).append("/").append(database)
                    .append("?useUnicode=true&characterEncoding=utf-8")
                    .toString();
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

}
